from flask import Flask, request, render_template, redirect, url_for,session,flash
import pymysql

app = Flask(__name__)
# 设置一个 secret_key 来加密 session 数据
app.secret_key = 'your_unique_secret_key'  # 确保将 'your_unique_secret_key' 替换为一个唯一的密钥
# 数据库连接配置
def get_db_connection():
    return pymysql.connect(
        host='localhost',
        user='root',
        password='123456',
        database='music_sys'
    )

# 登录页面
@app.route('/login', methods=['GET', 'POST'])
def login():
    if request.method == 'POST':
        username = request.form['username']
        password = request.form['password']

        # 连接数据库并查询用户
        conn = get_db_connection()
        cursor = conn.cursor()

        # 查询用户信息
        cursor.execute("SELECT user_identify FROM user_info WHERE user_name=%s AND user_paswod=%s", (username, password))
        user = cursor.fetchone()

        cursor.close()
        conn.close()

        if user:
            # 将用户信息存储到 session 中
            session['username'] = username
            session['user_identify'] = user[0]
            # 根据user_identify进行重定向
            if user[0] == 1:
                return redirect(url_for('band_info'))  # 乐队用户
            elif user[0] == 2:
                return redirect(url_for('fan_info'))  # 粉丝用户
        else:
            return "Invalid login credentials. Please try again."

    return render_template('login.html')

# 注册接口
@app.route('/register', methods=['GET', 'POST'])
def register():
    if request.method == 'POST':
        username = request.form['username']
        password = request.form['password']
        usertype = request.form['usertpye']

        # 连接数据库并检查用户名是否已经存在
        conn = get_db_connection()
        cursor = conn.cursor()

        # 查询用户名是否已存在
        cursor.execute("SELECT * FROM user_info WHERE user_name = %s", (username,))
        existing_user = cursor.fetchone()

        if existing_user:
            flash('Username already exists. Please choose a different one.', 'error')
            return redirect(url_for('register'))

        # 插入新用户数据到数据库
        cursor.execute("INSERT INTO user_info (user_name, user_paswod, user_identify) VALUES (%s, %s, %s)",
                       (username, password, usertype))  # 设置用户身份

        conn.commit()
        cursor.close()
        conn.close()

        flash('Registration successful. Please log in.', 'success')
        return redirect(url_for('login'))

    return render_template('register.html')

# 乐队用户页面

'''@app.route('/band_info')
def band_info():
    if 'username' in session:
        username = session['username']
        # 获取乐队相关信息，展示给用户
        return f"Welcome {username}, this is the band info page."
    return redirect(url_for('login'))  # 如果未登录，跳转到登录页面'''
@app.route('/band_info')
def band_info():
    if 'username' in session:
        username = session['username']

        # 获取数据库连接
        conn = get_db_connection()
        cursor = conn.cursor()

        # 查询用户所属的乐队信息
        cursor.execute("SELECT band_name, band_settime,band_intro FROM band_info WHERE band_master = %s", (username,))
        band_info = cursor.fetchone()

        # 查询该乐队的成员信息
        cursor.execute(
            "SELECT mem_name, mem_position ,mem_join FROM mem_info WHERE mem_master = %s", (username,))
        members = cursor.fetchall()

        #查询乐队参加的演唱会
        cursor.execute("SELECT concert_name, concert_time, concert_location FROM concert_info WHERE concert_name IN (SELECT concert_band_concert_name FROM concert_band WHERE concert_band_band_master = %s)", (username,))
        concerts = cursor.fetchall()



        #查询乐队发表的专辑
        cursor.execute("SELECT album_name, album_time, album_intro FROM album_info WHERE song_master = %s", (username,))
        albums = cursor.fetchall()

        cursor.close()
        conn.close()

        if band_info:
            band_name, band_settime ,band_intro = band_info
            return render_template('band_info.html',
                                   username=username,
                                   band_name=band_name,
                                   band_settime=band_settime,
                                   band_intro=band_intro,
                                   members=members,
                                   concerts=concerts,
                                   albums=albums
                                   )
        else:
            return "You are not part of a band."

    return redirect(url_for('login'))  # 如果未登录，跳转到登录页面

''''# 粉丝用户页面
@app.route('/fan_info')
def fan_info():
    return render_template('fan_info.html')
'''
@app.route('/fan_info')
def fan_info():
    if 'username' in session:
        username = session['username']

        # 获取数据库连接
        conn = get_db_connection()
        cursor = conn.cursor()

        # 查询粉丝的基本信息
        cursor.execute("SELECT fan_id, fan_name, fan_age, fan_gender, fan_occupy, fan_edu FROM fan_info WHERE fan_name = %s", (username,))
        fan_info = cursor.fetchone()

        # 查询粉丝参加的音乐会
        cursor.execute("SELECT concert_name, concert_time, concert_location FROM concert_info WHERE concert_name IN (SELECT fan_concert_concert_name FROM fan_concert WHERE fan_concert_fan_id IN (SELECT fan_id FROM fan_info WHERE fan_name = %s))", (username,))
        concerts = cursor.fetchall()

        # 查询粉丝评论过的专辑
        cursor.execute("SELECT fan_album_album_name, fan_mark, fan_review FROM fan_album WHERE fan_album_fan_id IN (SELECT fan_id FROM fan_info WHERE fan_name = %s)", (username,))
        albums = cursor.fetchall()

        cursor.close()
        conn.close()

        if fan_info:
            fan_id, fan_name, fan_age, fan_gender, fan_occupy, fan_edu = fan_info
            return render_template('fan_info.html',
                                   fan_id=fan_id,
                                   fan_name=fan_name,
                                   fan_age=fan_age,
                                   fan_gender=fan_gender,
                                   fan_occupy=fan_occupy,
                                   fan_edu=fan_edu,
                                   concerts=concerts,
                                   albums=albums)
        else:
            return render_template('datain.html')

    return redirect(url_for('login'))  # 如果未登录，跳转到登录页面



# 登出处理
@app.route('/logout')
def logout():
    # 清除 session 中的用户信息
    session.pop('username', None)
    session.pop('user_identify', None)
    # 重定向到登录页面
    return redirect(url_for('login'))


# 粉丝信息录入页面
@app.route('/datain', methods=['POST'])
def datain():
    if 'username' not in session:
        return redirect(url_for('login'))  # 如果未登录，跳转到登录页面

    if request.method == 'POST':
        # 获取表单数据
        fan_id = request.form['fan_id']
        fan_name = request.form['fan_name']
        fan_age = request.form['fan_age']
        fan_gender = request.form['fan_gender']
        fan_occupy = request.form['fan_occupy']
        fan_edu = request.form['fan_edu']

        # 假设 fan_id 是从 session 中获取的或者从数据库中生成
       # fan_id = session['username']  # 可以将粉丝的 username 当做 fan_id

        # 连接数据库并插入数据
        conn = get_db_connection()
        cursor = conn.cursor()

        try:
            # 插入粉丝基本信息到 fan 表
            cursor.execute(
                "INSERT INTO fan_info (fan_id, fan_name, fan_age, fan_gender, fan_occupy, fan_edu) "
                "VALUES (%s, %s, %s, %s, %s, %s)",
                (fan_id, fan_name, fan_age, fan_gender, fan_occupy, fan_edu)
            )
            conn.commit()
            flash('信息录入成功！', 'success')  # 提示用户信息录入成功
        except Exception as e:
            conn.rollback()  # 如果发生错误，回滚操作
            flash('信息录入失败，请重试！', 'error')  # 提示用户失败
            print(f"Error: {e}")

        cursor.close()
        conn.close()

        return redirect(url_for('fan_info'))  # 信息提交成功后，重定向回录入页面

    return render_template('login.html')


#我们将创建一个路由 /all_concerts，该路由会查询 concert_info 表中的所有音乐会信息并将其传递给前端页面。
@app.route('/all_concerts', methods=['GET'])
def all_concerts():
    # 获取数据库连接
    conn = get_db_connection()
    cursor = conn.cursor()

    # 查询所有演唱会信息
    cursor.execute("SELECT concert_name, concert_time, concert_location FROM concert_info")
    concerts = cursor.fetchall()

    cursor.close()
    conn.close()

    return render_template('add_concert.html', concerts=concerts)


#为了让用户能参加某个音乐会，我们需要一个后端接口 /add_concert，该接口将处理用户的参加操作。我们假设用户已经登录，并通过 session 存储了用户信息（例如：fan_id）。
@app.route('/add_concert', methods=['POST'])
def add_concert():
    concert_name = request.form['concert_name']  # 获取演唱会名称
    user_name = session['username']  # 获取当前登录的用户 ID（你可以根据需要调整）
    # 获取数据库连接
    conn = get_db_connection()
    cursor = conn.cursor()
    cursor.execute("SELECT fan_id FROM fan_info WHERE fan_name = %s",
                            (user_name,))
    fan_id = cursor.fetchone()
    # 插入粉丝参加的演唱会
    cursor.execute("INSERT INTO fan_concert (fan_concert_fan_id, fan_concert_concert_name) VALUES (%s, %s)",
                   (fan_id, concert_name))

    conn.commit()
    cursor.close()
    conn.close()

    flash("你已成功参加该演唱会！", "success")
    return redirect(url_for('fan_info'))  # 跳转到粉丝主页

@app.route('/all_albums', methods=['GET'])
def all_albums():
    # 获取数据库连接
    conn = get_db_connection()
    cursor = conn.cursor()

    # 查询所有专辑信息
    cursor.execute("SELECT album_name, album_time, album_intro FROM album_info")
    albums = cursor.fetchall()

    cursor.close()
    conn.close()

    return render_template('add_album_review.html', albums=albums)

@app.route('/add_album_review', methods=['POST'])
def add_album_review():
    album_name = request.form['album_name']  # 获取用户选择的专辑名称
    fan_name = session['username']  # 获取当前登录的粉丝用户名

    # 获取数据库连接
    conn = get_db_connection()
    cursor = conn.cursor()

    # 查询粉丝的 fan_id
    cursor.execute("SELECT fan_id FROM fan_info WHERE fan_name = %s", (fan_name,))
    fan_id = cursor.fetchone()
    if fan_id:
        fan_id = fan_id[0]
    else:
        flash("未找到粉丝信息，请重新登录。", "error")
        return redirect(url_for('login'))

    # 获取评论和评分
    review = request.form['review']  # 获取用户评论
    score = int(request.form['score'])  # 获取用户评分

    # 检查评论是否已存在
    cursor.execute("SELECT * FROM fan_album WHERE fan_album_album_name = %s AND fan_album_fan_id = %s",
                   (album_name, fan_id))
    existing_review = cursor.fetchone()

    if existing_review:
        flash("您已经对该专辑发表评论，无法重复评论。", "warning")
    else:
        # 插入评论数据
        cursor.execute("INSERT INTO fan_album (fan_album_album_name, fan_album_fan_id, fan_review, fan_mark) VALUES (%s, %s, %s, %s)",
                       (album_name, fan_id, review, score))
        conn.commit()
        flash("评论提交成功！", "success")

    cursor.close()
    conn.close()

    return redirect(url_for('fan_info'))  # 跳转到粉丝主页


@app.route('/add_mem', methods=['GET', 'POST'])
def add_mem():
    # 如果是 GET 请求，渲染添加成员页面
    if request.method == 'GET':
        return render_template('add_member.html')

    # 如果是 POST 请求，处理表单提交
    if request.method == 'POST':
        # 获取表单数据
        mem_name = request.form['mem_name']
        mem_gender = request.form['mem_gender']
        mem_age = int(request.form['mem_age'])
        mem_position = request.form['mem_position']
        mem_join = request.form['mem_join']
        band_master = session['username']  # 获取登录的队长用户名（从 session 中）

        if not band_master:
            flash("请先登录为队长", "error")
            return redirect(url_for('login'))  # 如果没有登录，重定向到登录页面

        # 获取数据库连接
        conn = get_db_connection()
        cursor = conn.cursor()

        # 插入成员数据到 member_info 表
        try:
            cursor.execute("""
                INSERT INTO mem_info (mem_name, mem_gender, mem_age, mem_position, mem_join, mem_master)
                VALUES (%s, %s, %s, %s, %s, %s)
            """, (mem_name, mem_gender, mem_age, mem_position, mem_join, band_master))

            conn.commit()  # 提交事务
            '''flash(f"成员 {mem_name} 添加成功！", "success")'''

        except pymysql.MySQLError as e:
            conn.rollback()  # 如果出现错误，回滚事务
            '''flash(f"添加成员失败: {str(e)}", "error")'''
        finally:
            cursor.close()
            conn.close()

        return redirect(url_for('band_info'))  # 添加成功后跳转到乐队主页

@app.route('/band_add_concert', methods=['GET', 'POST'])
def band_add_concert():
    if 'username' not in session:
        return redirect(url_for('login'))  # 如果用户未登录，则跳转到登录页

    if request.method == 'POST':
        # 获取表单数据
        concert_name = request.form['concert_name']
        concert_time = request.form['concert_time']
        concert_location = request.form['concert_location']
        band_master = session['username']  # 获取当前登录的乐队队长的用户名

        # 获取数据库连接
        conn = get_db_connection()
        cursor = conn.cursor()

        # 插入新的演唱会信息
        cursor.execute("INSERT INTO concert_info (concert_name, concert_time, concert_location, concert_band_master) VALUES (%s, %s, %s, %s)",
                       (concert_name, concert_time, concert_location, band_master))
        cursor.execute("INSERT INTO concert_band (concert_band_concert_name,concert_band_band_master) VALUES (%s, %s)",
                       (concert_name,band_master))
        conn.commit()

        cursor.close()
        conn.close()

        flash("演唱会已成功添加！", "success")
        return redirect(url_for('band_info'))  # 添加完毕后跳转回乐队信息页面

    return render_template('band_add_concert.html')





@app.route('/add_album', methods=['GET', 'POST'])
def add_album():
    if 'username' not in session:
        return redirect(url_for('login'))  # 如果用户未登录，则跳转到登录页

    if request.method == 'POST':
        # 获取表单数据
        album_name = request.form['album_name']
        album_time = request.form['album_time']
        album_intro = request.form['album_intro']
        song_master = session['username']  # 获取当前登录的乐队队长的用户名

        # 获取数据库连接
        conn = get_db_connection()
        cursor = conn.cursor()

        # 插入新的专辑信息
        cursor.execute("INSERT INTO album_info (album_name, album_time, album_intro, song_master) VALUES (%s, %s, %s, %s)",
                       (album_name, album_time, album_intro, song_master))
        conn.commit()

        cursor.close()
        conn.close()

        flash("专辑已成功添加！", "success")
        return redirect(url_for('band_info'))  # 添加完毕后跳转回乐队信息页面

    return render_template('add_album.html')


# 获取乐队的所有专辑及歌曲信息
@app.route('/get_all_album', methods=['GET', 'POST'])
def get_all_album():
    if 'username' not in session:
        return redirect(url_for('login'))  # 如果没有登录，重定向到登录页面

    username = session['username']  # 获取当前用户名

    # 获取乐队所有专辑信息
    connection = get_db_connection()
    with connection.cursor() as cursor:
        cursor.execute("""
            SELECT album_name, album_time, album_intro 
            FROM album_info
            WHERE song_master = %s
        """, (username,))
        albums = cursor.fetchall()

        # 获取每个专辑的歌曲信息
        album_songs = {}
        for album in albums:
            album_name = album[0]
            cursor.execute("""
                SELECT song_name, song_writer, song_release 
                FROM song_info
                WHERE song_belong = %s
            """, (album_name,))
            songs = cursor.fetchall()
            album_songs[album_name] = songs

    connection.close()

    if request.method == 'POST':
        # 添加歌曲信息
        album_name = request.form['album_name']
        song_name = request.form['song_name']
        song_writer = request.form['song_writer']
        song_release = request.form['song_release']

        # 将歌曲信息插入数据库
        connection = get_db_connection()
        with connection.cursor() as cursor:
            cursor.execute("""
                INSERT INTO song_info (song_name, song_writer, song_release, song_belong)
                VALUES (%s, %s, %s, %s)
            """, (song_name, song_writer, song_release, album_name))
            connection.commit()

        connection.close()
        flash('歌曲添加成功！')
        return redirect(url_for('get_all_album'))

    return render_template('get_all_album.html', albums=albums, album_songs=album_songs)




if __name__ == '__main__':
    app.run(debug=True)







